package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.NewsDao;
import com.cn.wanxi.model.NewsModel;
import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsImpl implements NewsDao {
    @Override
    public int add(NewsModel newsModel) {
        String sql = "insert into news (name,author,status,create_time,isTop,content,plate_id) " +
                "values('" + newsModel.getTitle() + "'," +
                "'" + newsModel.getAuthor() + "'," +
                "'" + newsModel.getStatus() + "'," +
                "'" + newsModel.getCreateTime() + "'," +
                "'" + newsModel.getIsTop() + "'," +
                "'" + newsModel.getContent() + "'," +
                "'" + newsModel.getPlateId() + "')";
        return JDBC.excuteUpdate(sql);
    }

    @Override
    public List<NewsModel> findAll(NewsModel newsModel, PageModel pageModel) {
        String sql = "select * from news where 1=1";

        if (!"".equals(newsModel.getTitle())) {
            sql += " and name like '%" + newsModel.getTitle() + "%'";
        }
//        name='%aa%' name='%aa'  name='aa%'  模糊查询 将=换成like
        if (!"".equals(newsModel.getContent())) {
            sql += " and content like '%" + newsModel.getContent() + "%'";
        }
        if (newsModel.getIsTop() != -1) {
            sql += " and isTop=" + newsModel.getIsTop();
        }
        if (newsModel.getStatus() != -1) {
            sql += " and status=" + newsModel.getStatus();
        }

        sql += " limit " + pageModel.getStartNum() + "," + pageModel.getPageSize();

        ResultSet resultSet = JDBC.getResultSet(sql);
        List<NewsModel> list = new ArrayList<>();
        try {
            //        判断resultSet是否有值
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                model.setId(resultSet.getInt("id"));
                model.setTitle(resultSet.getString("name"));
                model.setPlateId(resultSet.getInt("plate_id"));
                model.setIsTop(resultSet.getInt("isTop"));
                model.setStatus(resultSet.getInt("status"));
                model.setContent(resultSet.getString("content"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setAuthor(resultSet.getString("author"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return list;
    }

    /**
     * 查询总条数
     *
     * @param newsModel
     * @return
     */
    @Override
    public Integer getCount(NewsModel newsModel) {
        String sql = "select count(*) as count from news where 1=1";
        if (!"".equals(newsModel.getTitle())) {
            sql += " and name like '%" + newsModel.getTitle() + "%'";
        }
//        name='%aa%' name='%aa'  name='aa%'  模糊查询 将=换成like
        if (!"".equals(newsModel.getContent())) {
            sql += " and content like '%" + newsModel.getContent() + "%'";
        }
        if (newsModel.getIsTop() != -1) {
            sql += " and isTop=" + newsModel.getIsTop();
        }
        if (newsModel.getStatus() != -1) {
            sql += " and status=" + newsModel.getStatus();
        }
        ResultSet resultSet = JDBC.getResultSet(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return count;
    }

    @Override
    public List<NewsModel> getList() {
        String sql = "select id,name,author,status," +
                "DATE_FORMAT(create_time,'%m-%d-%Y %H:%i:%s') as create_time," +
                "isTop,content,plate_id " +
                "from news " +
                "order by create_time desc " +
                "limit 3";
        ResultSet resultSet = JDBC.getResultSet(sql);
        List<NewsModel> list = new ArrayList<>();
        try {
            //        判断resultSet是否有值
            while (resultSet.next()) {
                NewsModel model = new NewsModel();
                model.setId(resultSet.getInt("id"));
                model.setTitle(resultSet.getString("name"));
                model.setPlateId(resultSet.getInt("plate_id"));
                model.setIsTop(resultSet.getInt("isTop"));
                model.setStatus(resultSet.getInt("status"));
                model.setContent(resultSet.getString("content"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setAuthor(resultSet.getString("author"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return list;
    }
}
